package com.estore.daoImpl;

import java.util.List;
import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

import com.estore.dao.User_Dao;
import com.estore.entity.Account;
import com.estore.entity.Agent_Profile;
import com.estore.entity.FavoriteProduct;
import com.estore.entity.Role;
import com.estore.entity.User_Profile;
import com.estore.info.ProfileInfo;
import com.estore.mapper.AccountMapper;
import com.estore.mapper.Agent_ProfileMapper;
import com.estore.mapper.FavoriteProductMapper;
import com.estore.mapper.ForgetPwdMapper;
import com.estore.mapper.ProfileInfoMapper;
import com.estore.mapper.RoleMapper;
import com.estore.mapper.User_ProfileMapper;

public class User_DaoImpl implements User_Dao {

	private DataSource dataSource;
	private JdbcTemplate templateObject;

	public void setDataSource(DataSource datasource) {
		this.dataSource = datasource;
		this.templateObject = new JdbcTemplate(datasource);
	}

	// create user_profile
	public void insertUser_Profile(User_Profile profile) {
		String sql = "";
		if (profile.getSex().equals("male")) {
			sql = "insert into user_profile (lastname, firstname, dateofbirth, email, sex, address, phonenumber, regdate, avatar) value "
					+ "(?, ?, ?, ?, ?, ?, ?, ?, 'img/avatar/default-ava-male.jpg')";
		} else {
			sql = "insert into user_profile (lastname, firstname, dateofbirth, email, sex, address, phonenumber, regdate, avatar) value "
					+ "(?, ?, ?, ?, ?, ?, ?, ?, 'img/avatar/default-ava-female.jpg')";
		}
		Object[] list = new String[] { profile.getLastName(),
				profile.getFirstName(), profile.getDateOfBirth(),
				profile.getEmail(), profile.getSex(), profile.getAddress(),
				profile.getPhoneNumber(), profile.getRegDate() };
		templateObject.update(sql, list);
	}

	// ktra ID va pass
	public Account getAccount(String userID, String pass) {
		String sql = "select * from Account where account = ? and pwd =?";
		Account profile;
		try {
			profile = (Account) templateObject.queryForObject(sql,
					new Object[] { userID, pass }, new AccountMapper());
			return profile;
		} catch (EmptyResultDataAccessException ex) {
			System.out.println(ex);
		}
		return null;
	}

	// lay userprofile tu userID
	public User_Profile getUser_Profile(Integer userID) {

		String sql = "select * from user_profile where userid = ?";
		User_Profile profile = (User_Profile) templateObject.queryForObject(
				sql, new Object[] { userID }, new User_ProfileMapper());
		return profile;
	}

	// list toan bo user
	public List<User_Profile> listUser_Profile() {
		String sql = "select * from user_profile";
		List<User_Profile> profile = templateObject.query(sql,
				new User_ProfileMapper());
		return profile;
	}

	public void delete(Integer userID) {

	}

	// update profile
	public void updateProfile(Integer userID, String lastName,
			String firstName, String email, String dateOfBirth, String sex,
			String address, String phoneNumber) {
		String sql = "update user_profile set firstname=?, lastname=?, email=?, dateofbirth=?, sex=?, address=?, phonenumber=? where userid = ?";
		Object[] list = new String[] { firstName, lastName, email, dateOfBirth,
				sex, address, phoneNumber, userID.toString() };
		templateObject.update(sql, list);
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public JdbcTemplate getTemplateObject() {
		return templateObject;
	}

	public void setTemplateObject(JdbcTemplate templateObject) {
		this.templateObject = templateObject;
	}

	@Override
	public Role getRole(Integer userID) {
		String sql = "select * from role where userid = ?";
		Role role = templateObject.queryForObject(sql, new Object[] { userID },
				new RoleMapper());
		return role;
	}

	// get account by userID
	@Override
	public Account getAccount(Integer userID) {
		String sql = "select * from account where userid = ?";
		Account account = templateObject.queryForObject(sql,
				new Object[] { userID }, new AccountMapper());
		return account;
	}

	// add new account
	@Override
	public void insertAccount(Account account) {
		String sql = "insert into account (account, pwd, secretquestion, secretanswer) values (?, ?, ?, ?)";
		Object[] list = new String[] { account.getAccount(), account.getPwd(),
				account.getSecretQuestion(), account.getSecretAnswer() };
		templateObject.update(sql, list);
	}

	@Override
	public void insertRole(Role role) {
		Integer a = role.getRole();
		String sql = "insert into role (role, isbanned) values (?, 0)";
		Object[] list = new String[] { a.toString() };
		templateObject.update(sql, list);
	}

	@Override
	public boolean checkAccountAvail(String accountName) {
		String sql = "select * from account where account = ?";
		try {
			Account account = templateObject.queryForObject(sql,
					new Object[] { accountName }, new AccountMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public boolean checkEmailAvail(String email) {
		String sql = "select * from user_profile where email = ?";
		try {
			User_Profile user_profile = templateObject.queryForObject(sql,
					new Object[] { email }, new User_ProfileMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public void updateRole(Integer userID) {

		// String sql = "update role set isBanned = 1 where userid = ?";
		String sql = "select * from role where userid = ?";
		Role role = templateObject.queryForObject(sql,
				new Object[] { userID.toString() }, new RoleMapper());
		if (role.getIsBanned() == 0) {
			sql = "update role set isBanned = 1 where userid = ?";
			templateObject.update(sql, new Object[] { userID });
		} else if (role.getIsBanned() == 1) {
			sql = "update role set isBanned = 0 where userid = ?";
			templateObject.update(sql, new Object[] { userID });
		}
	}

	@Override
	public ProfileInfo getUserInfo(Integer UserID) {
		ProfileInfo info;
		String sql = "SELECT account.account, account.pwd, account.secretquestion, account.secretanswer,"
				+ " user_profile.firstname, user_profile.lastname, user_profile.email,"
				+ " user_profile.dateofbirth, user_profile.sex, user_profile.address,"
				+ " user_profile.phonenumber, role.role, role.isbanned, user_profile.avatar"
				+ " from user_profile"
				+ " join account on account.userid = user_profile.userid"
				+ " join role on role.userid = user_profile.userid"
				+ " where user_profile.userid = ?";
		Object[] list = new String[] { UserID.toString() };
		try {
			info = templateObject.queryForObject(sql, list,
					new ProfileInfoMapper());
			// System.out.println(info.getAccount());
			// System.out.println(info.getFirstName());
			// System.out.println(info.getLastName());
			// System.out.println(info.getPassword());
			// System.out.println(info.getRole());
			// System.out.println(info.getIsBanned());
			// System.out.println(info.getPhoneNumber());
			// System.out.println(info.getAddress());
			// System.out.println(info.getDateOfBirth());
			// System.out.println(info.getSecretAnswer());
			// System.out.println(info.getSecretQuestion());
			// System.out.println(info.getSex());
			return info;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public boolean checkForgetPwd(ProfileInfo info) {
		try {
			String sql = "select * from user_profile join account on"
					+ " user_profile.userid = account.userid"
					+ " where account.account = ? and user_profile.email = ?"
					+ " and account.secretQuestion = ? and account.secretanswer = ?";
			Object[] list = new String[] { info.getAccount(), info.getEmail(),
					info.getSecretQuestion(), info.getSecretAnswer() };
			ProfileInfo profileInfo = templateObject.queryForObject(sql, list,
					new ForgetPwdMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public void changePwd(String account, String pwd) {
		String sql = "update account set pwd = ? where account = ?";
		Object[] list = new String[] { pwd, account };
		templateObject.update(sql, list);
	}

	@Override
	public void changeProfile(ProfileInfo info) {
		String sql = "update user_profile set lastname = ?, firstname = ?, dateofbirth = ?, sex = ?, address = ?, phonenumber = ?"
				+ " where userid = ?";
		Integer userID = info.getUserID();
		Object[] list = new String[] { info.getLastName(), info.getFirstName(),
				info.getDateOfBirth(), info.getSex(), info.getAddress(),
				info.getPhoneNumber(), userID.toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public void changeAvatar(String linkAvatar, String userID) {
		String sql = "update user_profile set avatar = ? where userid = ?";
		try {
			templateObject.update(sql, new Object[] { linkAvatar, userID });
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public boolean checkSecret(Account account) {
		String sql = "select * from account where secretquestion = ? and secretanswer = ?"
				+ " and account = ?";
		try {
			Account acc = templateObject.queryForObject(sql, new Object[] {
					account.getSecretQuestion(), account.getSecretAnswer(),
					account.getAccount() }, new AccountMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public void changeEmail(String email, String account) {
		String sql = "update user_profile set email = ? where userid = ?";
		try {
			templateObject.update(sql, new Object[] { email, account });
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public void addFavoriteProduct(FavoriteProduct info) {
		String sql = "insert into favoriteproduct (productid, userid, type, adddate) values (?, ?, ?, ?)";
		Object[] list = new String[] { info.getProductID().toString(),
				info.getUserID().toString(), info.getType().toString(),
				info.getAddDate() };
		try{
			templateObject.update(sql, list);
		}catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public boolean checkFavoriteProduct(Integer productID, Integer userID, Integer type) {
		String sql = "select * from favoriteproduct where productid = ? and userid = ? and type =?";
		Object[] list = new String[] {productID.toString(), userID.toString(), type.toString()};
		FavoriteProduct favor;
		try {
			favor = templateObject.queryForObject(sql, list, new FavoriteProductMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public List<FavoriteProduct> getWatchListProduct(Integer userID, Integer type) {
		String sql = "select * from favoriteproduct where userid = ? and type = ?";
		Object[] list = new String[] {userID.toString(), type.toString()};
		try {
			List<FavoriteProduct> listProduct = templateObject.query(sql, list, new FavoriteProductMapper());
			return listProduct;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void removeFavoriteProduct(Integer userID, Integer productID, Integer type) {
		String sql = "delete from favoriteproduct where userid =? and productid =? and type =?";
		Object[] list = new String[] {userID.toString(), productID.toString(), type.toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public Agent_Profile getAgentProfile(Integer userID) {
		String sql = "select * from agent_profile where userid = ?";
		Object[] list = new String[] { userID.toString() };
		try {
			Agent_Profile agent = templateObject.queryForObject(sql, list, new Agent_ProfileMapper());
			return agent;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void endAllList(Integer agentID) {
		String sql = "update list set liststatus = 0 where agentid = ?";
		Object[] list = new String[] { agentID.toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

}
